import time
import pandas as pd
from docx import Document
from docx.shared import Inches #设置图像大小
import utils
import coa_template
import docwriter

def spec_add_information(doc,spec_info_df):
    docwriter.docx_add_heading(doc,u"Information")
    docwriter.docx_add_table(doc,spec_info_df,title=False)
    # Botanical Name 斜体
    for row in range(len(doc.tables[0].rows)):
        if 'Botanical' in doc.tables[0].cell(row,0).text:
            doc.tables[0].cell(row,1).paragraphs[0].runs[0].font.italic = True
            break

def spec_add_specification(doc,spec_spec_df):

    docwriter.docx_add_heading(doc,u'Each batch analyzed for below items')
    spec_spec_df.columns=['Item','Specification','Method']
    docwriter.docx_add_table(doc,spec_spec_df,(8.5,4,5))


def spec_add_annual(doc,spec_annual_df):
    docwriter.docx_add_heading(doc,u'Annually analyzed at a third-party lab for below items')
    spec_annual_df.columns=['Item','Specification','Method']
    docwriter.docx_add_table(doc,spec_annual_df,(8.5,4,5))#

def spec_add_StorageAndPackaging(doc,spec_snp_df):
    row_n = spec_snp_df.shape[0]
    for i in range(row_n):
        docwriter.docx_add_heading(doc,spec_snp_df.loc[i,"Item"])
        spec_par0 = doc.add_paragraph()
        spec_par0_run0 = spec_par0.add_run(spec_snp_df.loc[i,"Target"])

def bkd_Spec(df):
    productinfo = {"Product Code":df['Product Code'].values[0],
                "Product Name":df['Product Name'].values[0],
                'Document type':"Specification",
                "Doc date":time.strftime("%b %Y", time.localtime())}
    
    header_template = utils.generate_Header(productinfo)
    doc = Document(header_template)
    try:
        spec_info_df = df.loc[df["Type"]=="Information",["Item","Target"]]
        spec_info_df.reset_index(drop=True,inplace=True)
        spec_add_information(doc,spec_info_df)
    except Exception as e:
        print (e) 
    try:
        spec_spec_df = df.loc[df["Type"]=="Specification",["Item","Target", "Method"]].reset_index(drop=True)
        spec_spec_df = sort_spec_df(spec_spec_df)
        spec_add_specification(doc,spec_spec_df)
    except Exception as e:
        print (e) 
    try:
        spec_annual_df = df.loc[df["Type"]=="Annual Test Item",["Item","Target", "Method"]].reset_index(drop=True)
        spec_annual_df = sort_annual_df(spec_annual_df)
        spec_add_annual(doc,spec_annual_df)
    except Exception as e:
        print (e) 
    try:
        spec_snp_df = df.loc[df["Type"]=="Storage and Packaging",["Item","Target"]].reset_index(drop=True)
        spec_add_StorageAndPackaging(doc,spec_snp_df)
    except Exception as e:
        print (e) 
    
    doc.save(header_template)
    return header_template

def sort_spec_df(df:pd.DataFrame)->pd.DataFrame:
    #选出排在前面的项目对应的编号
    head_index=[df[df['Item'].str.contains(i,case=False)].index[0] 
                  for i in ['appearance','odor','particle size','loss on drying']
                  if len(df[df['Item'].str.contains(i,case=False)].index)!=0]
    # 选出溶剂残留的编号
    residue_index = list(df[df['Item'].str.contains('residue',case=False)].index)
    # 选出微生物项目的编号
    microbial_index = [df[df['Item'].str.contains(i,case=False)].index[0] 
                  for i in ['molds and yeast','yeast and molds','aerobic microbial','plate count']
                  if len(df[df['Item'].str.contains(i,case=False)].index)!=0]
    
    foot_index = residue_index + list(set(microbial_index))
    remain_index = list(set(df.index) - set(head_index)-set(foot_index))
    new_index = head_index+remain_index+foot_index
    df = df.loc[new_index,:]
    df.reset_index(drop=True,inplace=True)
    return df

def sort_annual_df(df:pd.DataFrame):
    head_index=[df[df['Item'].str.contains(i,case=False)].index[0] 
                  for i in ['pesticide', 'heavy metal', 'arsenic','lead', 'mercury', 'cadium']
                  if len(df[df['Item'].str.contains(i,case=False)].index)!=0]
    remain_index = list(set(df.index) - set(head_index))
    new_index = head_index+remain_index
    df = df.loc[new_index,:]
    df.reset_index(drop=True,inplace=True)
    return df
    

def write_spec(productcode):
    df_raw = utils.validate_product_info()
    df = df_raw.copy()
    file_ready =False
    if productcode in df["Product Code"].unique():
        df = df[df["Product Code"] == productcode]
        df = df.reset_index(drop=True)
        df=df.drop_duplicates()
        file_ready = utils.validate_df(df)
        if file_ready['coa']==True:
            filename = bkd_Spec(df)
            print("spec Generated")
        else: print("!Error. File not ready")

    else:
        print("!Error. No such code!")

    return filename

if __name__ == "__main__":
    write_spec('FI5303')